import pandas as pd


def split_phone_numbers(df):
    """
    Split rows where the phone column contains multiple phone numbers.
    Each number will be placed on a new row, with other data remaining unchanged.
    """
    # Check if 'phone' column exists
    if 'phone' not in df.columns:
        raise ValueError("The DataFrame does not contain a 'phone' column.")

    # Function to split phone numbers and create new rows
    def expand_phones(row):
        phones = str(row['手机']).split(',')
        return pd.DataFrame([row] * len(phones), index=range(len(phones)))

    # Apply the function to each row
    expanded_df = df.apply(expand_phones, axis=1).reset_index(drop=True)

    # Split the 'phone' column into separate rows
    expanded_df['手机'] = expanded_df['手机'].str.split(',').explode()

    return expanded_df


def process_excel(input_file, output_file):
    # Read the Excel file
    xls = pd.ExcelFile(input_file)

    # Create a writer object for the output Excel file
    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        for sheet_name in xls.sheet_names:
            # Read each sheet
            df = pd.read_excel(xls, sheet_name=sheet_name)

            # Process the 'phone' column
            processed_df = split_phone_numbers(df)

            # Write the processed DataFrame to the output Excel file
            processed_df.to_excel(writer, sheet_name=sheet_name, index=False)


if __name__ == '__main__':
    input_file = './data3/山东.xlsx'
    output_file = './data3/山东_output.xlsx'
    process_excel(input_file, output_file)
    print(f"Processed data saved to {output_file}")


# import pandas as pd
#
# def split_phone_numbers(df):
#     """
#     Split rows where the phone column contains multiple phone numbers.
#     Each number will be placed on a new row, with other data remaining unchanged.
#     """
#     # Check if 'phone' column exists
#     if 'phone' not in df.columns:
#         raise ValueError("The DataFrame does not contain a 'phone' column.")
#
#     # Function to split phone numbers and create new rows
#     def expand_phones(row):
#         phones = str(row['手机']).split(',')
#         return pd.DataFrame([row]*len(phones)).assign(phone=[phone.strip() for phone in phones])
#
#     # Apply the function to each row
#     expanded_df = df.apply(expand_phones, axis=1).reset_index(drop=True)
#     return expanded_df
#
# def process_excel(input_file, output_file):
#     # Read the Excel file
#     xls = pd.ExcelFile(input_file)
#
#     # Create a writer object for the output Excel file
#     with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
#         for sheet_name in xls.sheet_names:
#             # Read each sheet, skipping the first row
#             df = pd.read_excel(xls, sheet_name=sheet_name, skiprows=1)
#
#             # Process the phone numbers
#             processed_df = split_phone_numbers(df)
#
#             # Write the processed DataFrame to the new Excel file
#             processed_df.to_excel(writer, sheet_name=sheet_name, index=False)
#
# if __name__ == '__main__':
#     input_file = './data3/山东.xlsx'
#     output_file = './data3/山东_output.xlsx'
#     process_excel(input_file, output_file)
#     print(f"Processed data saved to {output_file}")